using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using FarPoint.Win;
using GrapeCity.Spreadsheet;

namespace Spread.DataValidation
{
    public partial class CellValidators : BaseSampleControl
    {
        Label _lNamesSheetDesc, _lSchedulesSheetDesc;
        public CellValidators()
        {
            InitializeComponent();
            Name = "Cell Validators";
            SetDescription();
            //Common Style Setup
            fpSpread.ActiveSheetIndex = 0;
            _sheetNames.ColumnHeader.Visible = false;
            _sheetNames.RowHeader.Visible = false;
            _sheetSched.ColumnHeader.Visible = false;
            _sheetSched.RowHeader.Visible = false;
            _sheetSched.AddCustomName("SchedNames", "Names!$C$2:$C$100", 0, 0, false, "");
            _sheetSched.AddCustomName("SchedDate", "SampleValidators!$F$4", 0, 0, false, "");

            //Sheet Introduction
            _sheetIntro.Rows[1].Height = _sheetIntro.Rows[10].Height = 50;
            _sheetIntro.Cells[1, 0].Font = _sheetIntro.Cells[10, 0].Font = new System.Drawing.Font("Segoi UI", 10, System.Drawing.FontStyle.Bold);
            _sheetIntro.Cells[1, 0].HorizontalAlignment = _sheetIntro.Cells[10, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
            _sheetIntro.Cells[1, 0].VerticalAlignment = _sheetIntro.Cells[10, 0].VerticalAlignment = CellVerticalAlignment.Center;

            //RangeValidator
            _sheetIntro.AddSpanCell(1, 0, 1, 10);
            _sheetIntro.Cells[1, 0].Text = "The range below has a RangeValidator applied to accept values between 1 and 5";
            LineNotify lineNotify = new LineNotify();
            RangeValidator rangeValidator = new RangeValidator()
            {
                MinValue = 1,
                MaxValue = 5,
            };
            ApplyCellValidator(_sheetIntro, 2, 1, 8, 5, rangeValidator, lineNotify);
            _sheetIntro.Cells[2, 1, 9, 5].BackColor = System.Drawing.Color.FromArgb(100, System.Drawing.Color.SandyBrown); ;

            //IncludeListValidator
            _sheetIntro.AddSpanCell(10, 0, 1, 10);
            _sheetIntro.Cells[10, 0].Text = "The range below has an IncludeListValidator applied to accept values 'hi' or 'hello' only";
            TipNotify tipNotify = new TipNotify
            {
                ToolTipIcon = ToolTipIcon.Error,
                ToolTipTitle = "Wrong Value!",
                ToolTipText = "This cell accepts only 'hi' or 'hello'",
            };
            IncludeListValidator listValidator = new IncludeListValidator
            {
                Candidates = new string[] { "hi", "hello" },
            };
            ApplyCellValidator(_sheetIntro, 11, 1, 8, 5, listValidator, tipNotify, lineNotify);
            _sheetIntro.Cells[11, 1, 18, 5].BackColor = System.Drawing.Color.FromArgb(100, System.Drawing.Color.LightBlue);

            //Sheet Names
            SetSheetStyle(_sheetNames);
            _sheetNames.SetClip(0, 0, 1, 3, "Employee\tName & Date\tShow Name");
            _sheetNames.SetClip(1,0,11,1, "Patricia Gabel \nPail Molden \nLyle Kier \nChandra Ordidge\nPennie Murdie \nKelci Kopelman \nTeirtza Infantino \nJamil Vasilkov \nTine Thickens \nDorisa Tompion \nBelle McGlaud");
            _sheetNames.Cells[1, 1, 99, 1].Formula = @"IF(A2<>"""",A2&SchedDate,"""")";
            _sheetNames.Cells[1, 2, 99, 2].Formula = @"IF(COUNTIF(SampleValidators!C:C,B2)>0,"""",Names!A2)";
            _sheetNames.FrozenRowCount = 1;

            //Sheet Schedules
            SetSheetStyle(_sheetSched);
            _sheetSched.SetClip(0, 0, 1, 3, "Work Date\tEmployee\tName & Date");
            _sheetSched.Cells[1, 2, 99, 2].Formula = @"IF(B2<>"""",B2&A2,"""")";
            _sheetSched.Cells["E4"].Text = "Scheduling Date-->";
            _sheetSched.Cells["F4"].Text = DateTime.Now.ToShortDateString();
            _sheetSched.Cells["E4:F4"].BackColor = System.Drawing.Color.LightYellow;

            for (int i = 1; i <= 10; i++)
                _sheetSched.Cells[i, 0].Text = DateTime.Now.AddDays(i-1).ToShortDateString();
            for (int i = 1; i < 100; i++)
            {
                if (_sheetSched.Cells[i, 0].Text == _sheetSched.Cells["F4"].Text)
                {
                    _sheetSched.Validations.Clear(i, 1, i, 1);
                    var validation = _sheetSched.Cells[i,1].Validation.Add(DataValidationType.List, DataValidationErrorStyle.Warning, DataValidationOperator.Equal, "=SchedNames", null);
                    validation.IgnoreBlank = true;
                }
            }
            _sheetSched.FrozenRowCount = 1;
            _sheetSched.CellChanged += _sheetSched_CellChanged;

            AddValidators();
            fpSpread.ActiveSheetChanged += FpSpread_ActiveSheetChanged;
        }

        private void FpSpread_ActiveSheetChanged(object sender, EventArgs e)
        {
            if(fpSpread.ActiveSheet.SheetName == "Names")
            {
                _lNamesSheetDesc.Visible = true;
                _lSchedulesSheetDesc.Visible = false;
                _pnlButtons.Visible = false;
            }
            else if(fpSpread.ActiveSheet.SheetName == "SampleValidators")
            {
                _lNamesSheetDesc.Visible = false;
                _lSchedulesSheetDesc.Visible = true;
                _pnlButtons.Visible = false;
            }
            else
            {
                _lNamesSheetDesc.Visible = false;
                _lSchedulesSheetDesc.Visible = false;
                _pnlButtons.Visible = true;
            }
        }

        private void SetDescription()
        {
            _lNamesSheetDesc = new Label() {Dock = DockStyle.Fill, Visible=false };
            _lNamesSheetDesc.Text = "TextLength Validator is set for cells in first column allowing 2 to 20 char" +
    "acters in name. If cell text(name) is shorter or longer, notify will be displayed.\r\n\r\n"+
    "Guidelines:\r\nWhen you want to schedule for 1 day, do as follow:\r\n"+
    "1. Enter the date you want to schedule for in F4 cell(colored yellow) of SampleValidators sheet.\r\n"+
    "2. The available employees for that day will appear in the dropdown list of 2nd column of SampleValidators sheet.On selecting an employee, his/her name will be removed from the list.\r\n" +
    "3. You can add additional employees to the Names sheet and additional work days in SampleValidators sheet.\r\n";

            _lSchedulesSheetDesc = new Label() {Dock = DockStyle.Fill, Visible=false};
            _lSchedulesSheetDesc.Text = "Scheduling Date Cell : \nThis cell has been named (SchedDate) so it is easier to use this cell in formulas on the worksheet.\nEnter date, you wish to schedule for, in this cell.\r\n\r\n"+
                "1. First Column uses RequiredType Validator to allow only dates\r\n"+
                "2. Cell F4 uses a RangeValidator to allow scheduling dates to be in the range of next 100 days.\r\n";

            this.pnlControls.Controls.Add(_lNamesSheetDesc);
            this.pnlControls.Controls.Add(_lSchedulesSheetDesc);
        }

        private void SetSheetStyle(SheetView sheet)
        {
            sheet.RowCount = 100;
            sheet.ColumnCount = 6;
            sheet.Columns[0, 5].Width = 120;

            sheet.Rows[0].Height = 30;
            sheet.Cells[0, 0, 0, 2].BackColor = System.Drawing.Color.Purple;
            sheet.Cells[0, 0, 0, 2].ForeColor = System.Drawing.Color.White;
            sheet.Cells[0, 0, 0, 2].Font = new System.Drawing.Font("Segoi UI", 10, System.Drawing.FontStyle.Bold);
            sheet.Cells[0, 0, 99, 2].Border = new LineBorder(System.Drawing.Color.Black, 1, true, true, true, true);
        }
        private void _sheetSched_CellChanged(object sender, SheetViewEventArgs e)
        {
            if ((e.Column == 0) || ((e.Column == 5) && (e.Row == 3)))
            {
                for (int i = 1; i < 100; i++)
                {
                    if (_sheetSched.Cells[i, 0].Text == _sheetSched.Cells[3, 5].Text)
                    {
                        _sheetSched.Validations.Clear(i, 1, i, 1);

                        var validation = _sheetSched.Cells[i, 1].Validation.Add(DataValidationType.List, DataValidationErrorStyle.Warning, DataValidationOperator.Equal, "=SchedNames", null);
                        validation.IgnoreBlank = true;
                    }
                }
            }
        }
        private void AddValidators()
        {
            LineNotify lineNotify = new LineNotify();
            
            //Length validator for Names
            TextLengthValidator nameLengthValidator = new TextLengthValidator
            {
                MinimumLength = 2,
                MaximumLength = 20,
            };
            MessageBoxNotify msgLengthNotify = new MessageBoxNotify
            {
                Message = "Please make sure that Name should have 2-20 characters",
                Caption = "Invalid Length",
                Icon = MessageBoxIcon.Stop,
                DoActionReason = ValidateReasons.CancelEdit | ValidateReasons.EndEdit,
            };
            ApplyCellValidator(_sheetNames, 1, 0, 99, 1, nameLengthValidator, lineNotify, msgLengthNotify);
            
            //RequiredType validator for DateTimes
            RequiredTypeValidator requiredTypValidator = new RequiredTypeValidator { RequiredType = typeof(DateTime) };
            MessageBoxNotify msgRequiredTypNotify = new MessageBoxNotify
            {
                Message = "Please enter Date only",
                Caption = "Invalid Type",
                Icon = MessageBoxIcon.Stop,
                DoActionReason = ValidateReasons.CancelEdit | ValidateReasons.EndEdit,
            };
            ApplyCellValidator(_sheetSched, 1, 0, 99, 1, requiredTypValidator, lineNotify, msgRequiredTypNotify);
            
            //RangeValidator for scheduling date
            var currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0);
            RangeValidator rangeDateValidator = new RangeValidator
            {
                MinValue = currentDate,
                MaxValue = currentDate.AddDays(100).AddSeconds(-1),
                RequiredType = typeof(DateTime),
            };
            MessageBoxNotify msgDateRangeNotify = new MessageBoxNotify
            {
                Message = $"Please enter a Date in range from {rangeDateValidator.MinValue} to {DateTime.Now.AddDays(99).ToShortDateString()}",
                Caption = "Invalid Date for scheduling",
                Icon = MessageBoxIcon.Stop,
                DoActionReason = ValidateReasons.CancelEdit | ValidateReasons.EndEdit,
            };

            ApplyCellValidator(_sheetSched, 3, 5, 1, 1, rangeDateValidator, lineNotify, msgDateRangeNotify);
        }

        private void _bAddEditValidator_Click(object sender, EventArgs e)
        {
            fpSpread.ShowCellValidatorsForm(this.ParentForm, true);
        }

        private void _bClearValidators_Click(object sender, EventArgs e)
        {
            var cellRange = fpSpread.Sheets[0].GetSelection(0);
            if(cellRange!=null)
                fpSpread.Sheets[0].ClearValidators(cellRange.Row, cellRange.Column, cellRange.RowCount, cellRange.ColumnCount);
        }

        private void ApplyCellValidator(SheetView sheet, int row, int col, int rowCount, int colCount, BaseDataValidator validator, params ValidateActionBase[] actions)
        {
            foreach (var action in actions)
                validator.Actions.Add(action);
            sheet.AddValidators(new FarPoint.Win.Spread.Model.CellRange(row, col, rowCount, colCount), validator);
        }
    }
}


//
//CellValidators.Designer.cs
//


namespace Spread.DataValidation
{
    partial class CellValidators
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread();
            this._sheetIntro = new FarPoint.Win.Spread.SheetView();
            this._sheetSched = new FarPoint.Win.Spread.SheetView();
            this._sheetNames = new FarPoint.Win.Spread.SheetView();
            this._bAddEditValidator = new System.Windows.Forms.Button();
            this._bClearValidators = new System.Windows.Forms.Button();
            this._pnlButtons = new System.Windows.Forms.Panel();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetIntro)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetSched)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetNames)).BeginInit();
            this._pnlButtons.SuspendLayout();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            this.pnlMain.Size = new System.Drawing.Size(603, 599);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this._pnlButtons);
            this.pnlControls.Size = new System.Drawing.Size(199, 599);
            // 
            // fpSpread
            // 
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this._sheetIntro,
            this._sheetSched,
            this._sheetNames});
            this.fpSpread.Size = new System.Drawing.Size(603, 599);
            this.fpSpread.TabIndex = 0;
            this.fpSpread.ActiveSheetIndex = 1;
            // 
            // _sheetIntro
            // 
            this._sheetIntro.Reset();
            this._sheetIntro.SheetName = "Introduction";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _sheetSched
            // 
            this._sheetSched.Reset();
            this._sheetSched.SheetName = "SampleValidators";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetSched.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetSched.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _sheetNames
            // 
            this._sheetNames.Reset();
            this._sheetNames.SheetName = "Names";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._sheetNames.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._sheetNames.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _bAddEditValidator
            // 
            this._bAddEditValidator.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bAddEditValidator.Location = new System.Drawing.Point(15, 20);
            this._bAddEditValidator.Name = "_bAddEditValidator";
            this._bAddEditValidator.Size = new System.Drawing.Size(130, 23);
            this._bAddEditValidator.TabIndex = 0;
            this._bAddEditValidator.Text = "Add/Edit Validators";
            this._bAddEditValidator.UseVisualStyleBackColor = true;
            this._bAddEditValidator.Click += new System.EventHandler(this._bAddEditValidator_Click);
            // 
            // _bClearValidators
            // 
            this._bClearValidators.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._bClearValidators.Location = new System.Drawing.Point(15, 73);
            this._bClearValidators.Name = "_bClearValidators";
            this._bClearValidators.Size = new System.Drawing.Size(130, 23);
            this._bClearValidators.TabIndex = 1;
            this._bClearValidators.Text = "Clear Validators";
            this._bClearValidators.UseVisualStyleBackColor = true;
            this._bClearValidators.Click += new System.EventHandler(this._bClearValidators_Click);
            // 
            // _pnlButtons
            // 
            this._pnlButtons.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._pnlButtons.Controls.Add(this._bAddEditValidator);
            this._pnlButtons.Controls.Add(this._bClearValidators);
            this._pnlButtons.Location = new System.Drawing.Point(5, 14);
            this._pnlButtons.Name = "_pnlButtons";
            this._pnlButtons.Size = new System.Drawing.Size(159, 127);
            this._pnlButtons.TabIndex = 2;
            // 
            // CellValidation
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "CellValidation";
            this.Size = new System.Drawing.Size(803, 599);
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetIntro)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetSched)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._sheetNames)).EndInit();
            this._pnlButtons.ResumeLayout(false);
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView _sheetNames;
        private FarPoint.Win.Spread.SheetView _sheetSched;
        private FarPoint.Win.Spread.SheetView _sheetIntro;
        private System.Windows.Forms.Button _bClearValidators;
        private System.Windows.Forms.Button _bAddEditValidator;
        private System.Windows.Forms.Panel _pnlButtons;
    }
}
